

********************************************************************************
************************************************************Part V: Final Sample

*** Main Specification (including job-weighted versions)
{
cd "~/DataFolder"

use KeyMeasure, clear

keep rssdid year
duplicates drop rssdid year, force
rename rssdid rssdhcr

*Merge with Bank Headqurter
merge m:1 rssdhcr year using BankHQ  
keep if _merge==3
drop _merge

*Merge with HMDA
merge 1:m rssdhcr year using HMDA_BHC_Sample_March2022_BranchInfo.dta // Downloaded from HMDA
keep if _merge==3
drop _merge

*Merge with KeyMeasure2
merge 1:1 rssdhcr year fips using KeyMeasure2
drop if _merge==2
drop _merge
replace spec_above5pctemp=0 if missing(spec_above5pctemp)
replace numspec_above5pctemp=0 if missing(numspec_above5pctemp)
replace aggemppct_above5pctemp=0 if missing(aggemppct_above5pctemp)
replace bankabove_above5pctemp=0 if missing(bankabove_above5pctemp)

merge 1:1 rssdhcr year fips using KeyMeasure2_AllInd 
drop if _merge==2
drop _merge
replace aggAllpct=0 if missing(aggAllpct)
distinct rssdhcr

*Merge with Data on County Distance
gen county1=stcnty
gen county2=fips
merge m:1 county1 county2 using distance2000.dta
drop if _merge==2
replace mi_to_county=0 if missing(mi_to_county)
drop _merge
gen distance=log(1+mi_to_county)

*Merge with Bank Controls
merge m:1 rssdhcr year using BankControls.dta // Downloaded from RIS
drop if _merge==2
drop _merge

gen size = log(asset/1000)
gen loan=lnlsgr/asset
gen deposit=dep/asset
gen p_ci=lnci/lnlsgr
gen p_con=lncon/lnlsgr
gen p_re=lnre/lnlsgr
gen roa=ibefxtr/asset
gen liquid=(sc+trade+freporcr)/asset

drop if missing(fips)
egen bank_county=group(rssdhcr fips)
tsset bank_county year

gen l_total_volume=l.total_volume
gen l_total_volume_approve=l.total_volume_approve
gen l_total_applications=l.total_applications
gen l_total_approve=l.total_approve
gen f_total_volume=f.total_volume
gen f_total_volume_approve=f.total_volume_approve
gen f_total_applications=f.total_applications
gen f_total_approve=f.total_approve

*Sample Selection
drop if total_applications<5   
keep if ((year>=1999) & (year<=2017))

*Additional variables
*Merge with Bank SBL 
merge 1:1 rssdhcr fips year using SBL.dta
drop if _merge==2
drop _merge
replace rssdhcr_ycty_num=0 if missing(rssdhcr_ycty_num)
replace rssdhcr_ycty_amt=0 if missing(rssdhcr_ycty_amt)
gen numsbl=log(1+rssdhcr_ycty_num)
gen volsbl=log(1+rssdhcr_ycty_amt)

*Merge with Retaining
merge 1:1 rssdhcr year fips using Retain.dta
sort rssdhcr fips year
bysort rssdhcr fips: gen lretain=retain[_n-1]
bysort rssdhcr fips: gen l2retain=retain[_n-2]
bysort rssdhcr fips: gen l3retain=retain[_n-3]
replace lretain = (lretain+l2retain+l3retain)/3
drop l2retain l3retain
keep if _merge==3
drop _merge

*Key Dependent Variables
tsset bank_county year

gen volume=log(total_volume_approve/1000) 
gen raw_volume=total_volume_approve/1000 
gen num=log(total_approve)
gen raw_num=total_approve
gen lag_volume = l.volume
gen lag_num = l.num
gen chg_volume = volume - lag_volume
gen chg_num = num - lag_num

rename numbankcountybranch numbranch
gen branch=log(1+numbranch)

winsor2(approve_rate_n approve_rate_v volume raw_volume num raw_num lag_volume lag_num chg_volume chg_num numsbl volsbl numspec aggf3pct bankabove retain lretain riskiness male avg_minority distance numbranch branch size loan deposit p_ci p_con p_re roa liquid), by(year) cuts(2.5 97.5) replace

drop if missing(num)

*FEs
egen hqcnty_county = group(stcnty fips)
egen bank_year=group(rssdhcr year)
egen county_year=group(fips year)
egen hqcnty_year = group(stcnty year)
gen st_fips=int(fips/1000)
egen bank_state=group(rssdhcr st_fips)
egen bank_cnty=group(rssdhcr fips)

save RegData.dta, replace

}
*


*** Size-Adjusted weight as bank specialization 
{
*Sample Construction
cd "~/DataFolder"

use KeyMeasure_wt, replace

keep rssdid year
duplicates drop rssdid year, force
rename rssdid rssdhcr

*Merge with Bank Headqurter
merge m:1 rssdhcr year using BankHQ   
keep if _merge==3
drop _merge

*Merge with HMDA
merge 1:m rssdhcr year using HMDA_BHC_Sample_March2022_BranchInfo.dta // Downloaded from HMDA
keep if _merge==3
drop _merge

*Merge with KeyMeasure2
merge 1:1 rssdhcr year fips using KeyMeasure2_wt
drop if _merge==2
drop _merge
replace spec=0 if missing(spec)

*Merge with Data on County Distance
gen county1=stcnty
gen county2=fips
merge m:1 county1 county2 using distance2000.dta
drop if _merge==2
replace mi_to_county=0 if missing(mi_to_county)
drop _merge
gen distance=log(1+mi_to_county)

*Merge with Bank Controls
merge m:1 rssdhcr year using BankControls.dta // Downloaded from RIS
drop if _merge==2
drop _merge

gen size = log(asset/1000)
gen loan=lnlsgr/asset
gen deposit=dep/asset
gen p_ci=lnci/lnlsgr
gen p_con=lncon/lnlsgr
gen p_re=lnre/lnlsgr
gen roa=ibefxtr/asset
gen liquid=(sc+trade+freporcr)/asset

drop if missing(fips)
egen bank_county=group(rssdhcr fips)
tsset bank_county year

gen l_total_volume=l.total_volume
gen l_total_volume_approve=l.total_volume_approve
gen l_total_applications=l.total_applications
gen l_total_approve=l.total_approve
gen f_total_volume=f.total_volume
gen f_total_volume_approve=f.total_volume_approve
gen f_total_applications=f.total_applications
gen f_total_approve=f.total_approve

*Sample Selection
drop if total_applications<5   
keep if ((year>=1999) & (year<=2017))

*Additional variables
*Merge with Bank SBL 
merge 1:1 rssdhcr fips year using SBL.dta
drop if _merge==2
drop _merge
replace rssdhcr_ycty_num=0 if missing(rssdhcr_ycty_num)
replace rssdhcr_ycty_amt=0 if missing(rssdhcr_ycty_amt)
gen numsbl=log(1+rssdhcr_ycty_num)
gen volsbl=log(1+rssdhcr_ycty_amt)

*Merge with Retaining
merge 1:1 rssdhcr year fips using Retain.dta
sort rssdhcr fips year
bysort rssdhcr fips: gen lretain=retain[_n-1]
bysort rssdhcr fips: gen l2retain=retain[_n-2]
bysort rssdhcr fips: gen l3retain=retain[_n-3]
replace lretain = (lretain+l2retain+l3retain)/3
drop l2retain l3retain
keep if _merge==3
drop _merge

*Key Dependent Variables
tsset bank_county year

gen volume=log(total_volume_approve/1000)
gen num=log(total_approve)
gen lag_volume = l.volume
gen lag_num = l.num
gen chg_volume = volume - lag_volume
gen chg_num = num - lag_num

*Variables
rename numbankcountybranch numbranch
gen branch=log(1+numbranch)

*Winsorize
winsor2(approve_rate_n approve_rate_v volume num lag_volume lag_num chg_volume chg_num numsbl volsbl retain lretain riskiness male avg_minority distance numbranch branch size loan deposit p_ci p_con p_re roa liquid), by(year) cuts(2.5 97.5) replace

drop if missing(num)

*FEs
egen bank_year=group(rssdhcr year)
egen county_year=group(fips year)
gen st_fips=int(fips/1000)
egen bank_state=group(rssdhcr st_fips)

save RegData_wt.dta, replace

}










*** Government Loan Sample
{
*Prepare HMDA
cd "~/DataFolder"

use HMDA_BHC_Sample_Gov2_July2023, clear  // Downloaded from HMDA

merge 1:1 rssdhcr year fips using Bank_Branch, keepusing(numbankcountybranch)
drop if _merge==2
drop _merge
keep if year>=1994
replace numbankcountybranch=0 if missing(numbankcountybranch)

merge m:1 rssdhcr year using Bank_Branch_AGG, keepusing(numbankbranch numbankcty numbanksts)
keep if _merge==3
drop _merge

save HMDA_BHC_Sample_Gov2_July2023_BranchInfo, replace 

*Sample Construction
use KeyMeasure, clear

keep rssdid year
duplicates drop rssdid year, force
rename rssdid rssdhcr

*Merge with Bank Headqurter
merge m:1 rssdhcr year using BankHQ 
keep if _merge==3
drop _merge

*Merge with HMDA
merge 1:m rssdhcr year using HMDA_BHC_Sample_Gov2_July2023_BranchInfo
keep if _merge==3
drop _merge

*Merge with KeyMeasure2
merge 1:1 rssdhcr year fips using KeyMeasure2
drop if _merge==2
drop _merge
replace spec_above5pctemp=0 if missing(spec_above5pctemp)
replace numspec_above5pctemp=0 if missing(numspec_above5pctemp)
replace aggemppct_above5pctemp=0 if missing(aggemppct_above5pctemp)
replace bankabove_above5pctemp=0 if missing(bankabove_above5pctemp)

*Merge with Data on County Distance
gen county1=stcnty
gen county2=fips
merge m:1 county1 county2 using distance2000.dta
drop if _merge==2
replace mi_to_county=0 if missing(mi_to_county)
drop _merge
gen distance=log(1+mi_to_county)

*Merge with Bank Controls
merge m:1 rssdhcr year using BankControls.dta
drop if _merge==2
drop _merge

gen size = log(asset/1000)
gen loan=lnlsgr/asset
gen deposit=dep/asset
gen p_ci=lnci/lnlsgr
gen p_con=lncon/lnlsgr
gen p_re=lnre/lnlsgr
gen roa=ibefxtr/asset
gen liquid=(sc+trade+freporcr)/asset

drop if missing(fips)
egen bank_county=group(rssdhcr fips)
tsset bank_county year

gen l_total_volume=l.total_volume
gen l_total_volume_approve=l.total_volume_approve
gen l_total_applications=l.total_applications
gen l_total_approve=l.total_approve
gen f_total_volume=f.total_volume
gen f_total_volume_approve=f.total_volume_approve
gen f_total_applications=f.total_applications
gen f_total_approve=f.total_approve

*Sample Selection
drop if total_applications<5   
keep if ((year>=1999) & (year<=2017))

*Additional variables
*Merge with Bank SBL 
merge 1:1 rssdhcr fips year using SBL.dta
drop if _merge==2
drop _merge
replace rssdhcr_ycty_num=0 if missing(rssdhcr_ycty_num)
replace rssdhcr_ycty_amt=0 if missing(rssdhcr_ycty_amt)
gen numsbl=log(1+rssdhcr_ycty_num)
gen volsbl=log(1+rssdhcr_ycty_amt)

*Merge with Retaining
merge 1:1 rssdhcr year fips using Retain.dta
sort rssdhcr fips year
bysort rssdhcr fips: gen lretain=retain[_n-1]
bysort rssdhcr fips: gen l2retain=retain[_n-2]
bysort rssdhcr fips: gen l3retain=retain[_n-3]
replace lretain = (lretain+l2retain+l3retain)/3
drop l2retain l3retain
keep if _merge==3
drop _merge

*Key Dependent Variables
tsset bank_county year

gen volume=log(total_volume_approve/1000) 
gen raw_volume=total_volume_approve/1000 
gen num=log(total_approve)
gen raw_num=total_approve
gen lag_volume = l.volume
gen lag_num = l.num
gen chg_volume = volume - lag_volume
gen chg_num = num - lag_num

rename numbankcountybranch numbranch
gen branch=log(1+numbranch)

winsor2(approve_rate_n approve_rate_v volume raw_volume num raw_num lag_volume lag_num chg_volume chg_num numsbl volsbl numspec aggf3pct bankabove retain lretain riskiness male avg_minority distance numbranch branch size loan deposit p_ci p_con p_re roa liquid), by(year) cuts(2.5 97.5) replace

drop if missing(num)

*FEs
egen bank_year=group(rssdhcr year)
egen county_year=group(fips year)
gen st_fips=int(fips/1000)
egen bank_state=group(rssdhcr st_fips)

save RegData_gov, replace
}
*

